﻿IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[esf_sso].[VEnterprise]'))
	DROP VIEW [esf_sso].[VEnterprise]
GO

-- SELECT * FROM [esf_sso].[VEnterprise]

CREATE VIEW [esf_sso].[VEnterprise] AS
	SELECT 
		VBR.ID							Ent_ID
		,VBR.Code						Ent_Code
		,VBR.Name						Ent_Name
		,VBR.Description				Ent_Description
		,VBR.IsEnabled					Ent_IsEnabled
		,VBR.CreationDateTime			Ent_CreationDateTime
		,VBR.LastUpdateDateTime			Ent_LastUpdateDateTime
		
		,E.IdentificationNumber			Ent_IdentificationNumber
		,E.IdentificationNumberDigit	Ent_IdentificationNumberDigit
		
		,VIT.ID							Ent_IdType_ID
		,VIT.Code						Ent_IdType_Code
		,VIT.Name						Ent_IdType_Name
		
		,VBR.Loc_ID						Ent_Loc_ID
		,VBR.Loc_Name					Ent_Loc_Name
		,VBR.Loc_CityName				Ent_Loc_CityName
		,VBR.Loc_PhoneNumber			Ent_Loc_PhoneNumber
		,VBR.Loc_CellPhoneNumber		Ent_Loc_CellPhoneNumber
		,VBR.Loc_Address				Ent_Loc_Address
		,VBR.Loc_Cnt_ID					Ent_Loc_Cnt_ID
		,VBR.Loc_Cnt_Name				Ent_Loc_Cnt_Name
		,VBR.Loc_City_ID				Ent_Loc_City_ID
		,VBR.Loc_City_Name				Ent_Loc_City_Name
		
		,VBR.UserCreationAlias			Ent_Usr_CreationAlias
		,VBR.UserLastUpdateAlias			Ent_Usr_LastUpdateAlias
	FROM
		esf_sso.[VLocatedBasicReference] VBR
	INNER JOIN
		esf_sso.Enterprise E ON E.EnterpriseID = VBR.ID
	INNER JOIN
		esf_sso.VIdentificationType VIT ON VIT.ID = E.IdentificationTypeID
	WHERE
		VBR.BasicReferenceTypeID = 6
GO